using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    public class ClassDAO : BaseDAO
    {
        public ClassDAO()
        {
        }

        public Entities.Class CreateClassFromReader(IDataReader reader)
        {
            Entities.Class _class = new Entities.Class();
            _class.ClassID = (string)reader["cClassID"];
            _class.ClassName = (string)reader["vClassName"];
            _class.CourseID = (string)reader["cCourseID"];
            return _class;
        }

        public void InsertClass(Entities.Class _class)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertClass", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@vClassName", _class.ClassName));
                command.Parameters.Add(new SqlParameter("@cCourseID", _class.CourseID));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void UpdateClass(Entities.Class _class)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateClass", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@cClassID", _class.ClassID));
                command.Parameters.Add(new SqlParameter("@vClassName", _class.ClassName));
                command.Parameters.Add(new SqlParameter("@cCourseID", _class.CourseID));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void DeleteClass(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteClass", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@cClassID", sClassID));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public Entities.Class GetClassByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetClassByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("cClassID", sClassID));
                connection.Open();
                Entities.Class _class = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        _class = CreateClassFromReader(reader);
                    }
                }
                return _class;
            }
        }

        public List<Entities.Class> GetClassByCourseID(string sCourseID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetClassByCourseID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("cCourseID", sCourseID));
                connection.Open();
                List<Entities.Class> classes = new List<Entities.Class>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        classes.Add(CreateClassFromReader(reader));
                    }
                }
                return classes;
            }
        }

        public List<Entities.Class> GetAllClasses()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllClasses", connection);
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                List<Entities.Class> classes = new List<Entities.Class>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        classes.Add(CreateClassFromReader(reader));
                    }
                }
                return classes;
            }
        }

        public DataTable GetAllStudentsOfClass(string sClassID,string sSubjectID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllStudentsOfClass", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@cClassID", sClassID));
                command.Parameters.Add(new SqlParameter("@cSubjectID", sSubjectID));
                connection.Open();
                DataTable dataTable = new DataTable();
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }
    }
}
